package com.baayso.bms.common.dbutils;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.slf4j.Logger;

import com.baayso.bms.common.db.JdbcUtil;
import com.baayso.bms.common.db.exception.DBException;
import com.baayso.bms.common.log.Log;
import com.baayso.bms.common.util.SQLHelper;
import com.baayso.bms.page.PageBean;

/**
 * DbUtils操作数据库工具类。
 * 
 * @author ChenFangjie (2016/3/4 11:18)
 * 
 * @since 1.2.0
 * @version 1.2.0
 */
public class QueryHelper {

    private static final Logger log = Log.get();

    private static final QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());

    @SuppressWarnings("serial")
    private final static List<Class<?>> PRIMITIVE_CLASSES = new ArrayList<Class<?>>() {
        {
            add(Long.class);
            add(Integer.class);
            add(String.class);
            add(java.util.Date.class);
            add(java.sql.Date.class);
            add(java.sql.Timestamp.class);
        }
    };

    private final static boolean isPrimitive(Class<?> cls) {
        return cls.isPrimitive() || PRIMITIVE_CLASSES.contains(cls);
    }

    /**
     * <pre>
     * Executes the given INSERT, UPDATE, or DELETE SQL statement without
     * any replacement parameters. The <code>Connection</code> is retrieved
     * from the <code>DataSource</code> set in the constructor.  This
     * <code>Connection</code> must be in auto-commit mode or the update will
     * not be saved.
     * </pre>
     *
     * @param sql
     *            The SQL statement to execute.
     * @return The number of rows updated.
     */
    public static int update(String sql) {
        return update(sql, (Object[]) null);
    }

    /**
     * <pre>
     * Executes the given INSERT, UPDATE, or DELETE SQL statement. The
     * <code>Connection</code> is retrieved from the <code>DataSource</code>
     * set in the constructor.  This <code>Connection</code> must be in
     * auto-commit mode or the update will not be saved.
     * </pre>
     * 
     * @param sql
     *            The SQL statement to execute.
     * @param params
     *            Initializes the PreparedStatement's IN (i.e. '?') parameters.
     * @return The number of rows updated.
     */
    public static int update(final String sql, Object... params) {
        int count = 0;

        try {
            count = runner.update(sql, params);
        }
        catch (SQLException e) {
            throw new DBException(e);
        }

        return count;
    }

    /**
     * <pre>
     * Executes the given SELECT SQL without any replacement parameters.
     * The <code>Connection</code> is retrieved from the
     * <code>DataSource</code> set in the constructor.
     * </pre>
     * 
     * @param <T>
     *            The type of object that the handler returns
     * @param sql
     *            The SQL statement to execute.
     * @param rsh
     *            The handler used to create the result object from the <code>ResultSet</code>.
     * @return An object generated by the handler.
     */
    public static <T> T query(String sql, ResultSetHandler<T> rsh) {
        return query(sql, rsh, (Object[]) null);
    }

    /**
     * <pre>
     * Executes the given SELECT SQL query and returns a result object.
     * The <code>Connection</code> is retrieved from the
     * <code>DataSource</code> set in the constructor.
     * </pre>
     * 
     * @param <T>
     *            The type of object that the handler returns
     * @param sql
     *            The SQL statement to execute.
     * @param rsh
     *            The handler used to create the result object from the <code>ResultSet</code>.
     * @param params
     *            Initialize the PreparedStatement's IN parameters with this array.
     * @return An object generated by the handler.
     */
    public static <T> T query(String sql, ResultSetHandler<T> rsh, Object... params) {
        T entity = null;

        try {
            entity = runner.query(sql, rsh, params);
        }
        catch (SQLException e) {
            throw new DBException(e);
        }

        return entity;
    }

    /**
     * <pre>
     * Execute a batch of SQL INSERT, UPDATE, or DELETE queries. The
     * <code>Connection</code> is retrieved from the <code>DataSource</code>
     * set in the constructor.  This <code>Connection</code> must be in
     * auto-commit mode or the update will not be saved.
     * </pre>
     *
     * @param sql
     *            The SQL to execute.
     * @param params
     *            An array of query replacement parameters. Each row in this array is one set of batch replacement values.
     * @return The number of rows updated per statement.
     * 
     * @see org.apache.commons.dbutils.QueryRunner#batch(String, Object[][])
     */
    public static int[] batch(String sql, Object[][] params) {
        try {
            return runner.batch(sql, params);
        }
        catch (SQLException e) {
            throw new DBException(e);
        }
    }

    public static <T> T query(Class<T> beanClass, String sql) {
        return query(beanClass, sql, (Object[]) null);
    }

    public static <T> T query(Class<T> beanClass, String sql, Object... params) {
        ResultSetHandler<T> rsh = isPrimitive(beanClass) ? new ScalarHandler<T>() : new BeanHandler<T>(beanClass);
        return query(sql, rsh, params);
    }

    public static <T> List<T> queryForList(Class<T> beanClass, String sql) {
        return queryForList(beanClass, sql, (Object[]) null);
    }

    public static <T> List<T> queryForList(Class<T> beanClass, String sql, Object... params) {
        return query(sql, new BeanListHandler<T>(beanClass), params);
    }

    /**
     * 分页查询（数据列表及分页信息封装于PageBean中）
     * 
     * @param beanClass
     *            需要查询数据的类型
     * @param pageNum
     *            当前页码
     * @param pageSize
     *            每页显示记录数
     * @param sqlHelper
     *            封装了分页查询语句以及查询条件的SQLHelper对象
     * @return 分页信息对象
     */
    public static <T> PageBean<T> pagination(Class<T> beanClass, int pageNum, int pageSize, SQLHelper sqlHelper) {

        if (sqlHelper == null) {
            throw new DBException("SQLHelper must not be null.");
        }

        log.debug("Query Count SQL: {}", sqlHelper.getQueryCountSql());

        // SQL语句使用的'参数列表'
        Object[] params = sqlHelper.getParameters().toArray();

        String paginationQuerySql = "";

        switch (JdbcUtil.getDbType()) {
            case MYSQL:
                paginationQuerySql = sqlHelper.getMySQLPaginationQueryListSql(pageNum, pageSize);
                break;
            case ORACLE:
                paginationQuerySql = sqlHelper.getOraclePaginationQueryListSql(pageNum, pageSize);
                break;
            case MSSQLSERVER:
                paginationQuerySql = sqlHelper.getSQLServerPaginationQueryListSql(pageNum, pageSize);
                break;
            case SQLITE:
                paginationQuerySql = sqlHelper.getSQLitePaginationQueryListSql(pageNum, pageSize);
                break;
            case POSTGRESQL:
                paginationQuerySql = sqlHelper.getPostgreSQLPaginationQueryListSql(pageNum, pageSize);
                break;
            case SYSBASE:
                paginationQuerySql = sqlHelper.getSysbasePaginationQueryListSql(pageNum, pageSize);
                break;
            case UNKNOWN:
                throw new DBException("Unknown database type.");
            default:
                throw new AssertionError("Program should not run into here.");
        }

        log.debug("Pagination Query SQL: {}", paginationQuerySql);

        List<T> recordList = queryForList(beanClass, paginationQuerySql, params);

        Long totalRecord = query(Long.class, sqlHelper.getQueryCountSql(), params);

        return new PageBean<T>(pageNum, pageSize, recordList, totalRecord == null ? 0L : totalRecord);
    }

}
